package net.huashitong.gradePoint.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import jodd.util.StringUtil;
import net.huashitong.gradePoint.databean.DepartRank;
import net.huashitong.gradePoint.entity.ScoreSummary;
import net.huashitong.gradePoint.filter.ScoreSummaryFilter;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class ScoreSummaryDao extends StringDao<ScoreSummary> {

    public List<ScoreSummary> queryByFilter(ScoreSummaryFilter filter){
        GenericQuery query = createQuery("obj");
        if(StringUtils.isNotBlank(filter.getCheckDept())){
            query.append(" and checkDept like:checkDept").setParam("checkDept",
                    StringUtils.getLikewords(filter.getCheckDept()));
        }
        if(StringUtils.isNotBlank(filter.getCheckObject())){
            query.append(" and checkObject like:checkObject").setParam("checkObject",
                    StringUtils.getLikewords(filter.getCheckObject()));
        }
        if(StringUtils.isNotBlank(filter.getSummaryDate())){
            query.append(" and summaryDate =:summaryDate").setParam("summaryDate",
                    filter.getSummaryDate());
        }
        if(StringUtils.isNotBlank(filter.getUserId())){
            query.append(" and userId =:userId").setParam("userId",
                    filter.getUserId());
        }
        if(StringUtils.isNotBlank(filter.getBranchType())){
            query.append(" and branchType =:branchType").setParam("branchType",
                    filter.getBranchType());
        }
        if(StringUtils.isBlank(filter.getPaging().getSortCol())){
            filter.getPaging().setSortCol("rank+0");
        }
        return query.listResult(filter);
    }

    public List<ScoreSummary> queryByFilterNoPage(ScoreSummaryFilter filter){
        GenericQuery query = createQuery("obj");
        if(StringUtils.isNotBlank(filter.getCheckDept())){
            query.append(" and checkDept like:checkDept").setParam("checkDept",
                    StringUtils.getLikewords(filter.getCheckDept()));
        }
        if(StringUtils.isNotBlank(filter.getCheckObject())){
            query.append(" and checkObject like:checkObject").setParam("checkObject",
                    StringUtils.getLikewords(filter.getCheckObject()));
        }
        if(StringUtils.isNotBlank(filter.getSummaryDate())){
            query.append(" and summaryDate =:summaryDate").setParam("summaryDate",
                    filter.getSummaryDate());
        }
        if(StringUtils.isNotBlank(filter.getUserId())){
            query.append(" and userId =:userId").setParam("userId",
                    filter.getUserId());
        }
        if(StringUtils.isNotBlank(filter.getBranchType())){
            query.append(" and branchType =:branchType").setParam("branchType",
                    filter.getBranchType());
        }
        if(ObjectUtils.isNotBlank(filter.getPaging())){
            if(StringUtils.isBlank(filter.getPaging().getSortCol())){
                filter.getPaging().setSortCol("rank+0");
            }
        }else{
            query.append(" ORDER BY rank+0 asc");
        }
        return query.listResult();
    }

    public List<ScoreSummary> queryScoreSummaryByIds(List<String> ids){
        GenericQuery query = createQuery("obj");
        query.append("and obj.sysId in(:ids)").setParamList("ids", ids);
        return query.listResult();
    }


    public List<DepartRank> queryDepartRank(ScoreSummaryFilter filter){
        StringBuffer sqlcount = new StringBuffer();
        sqlcount.append("SELECT count(check_dept) FROM t_scoresummary S  where 1 = 1 ");
        if(StringUtils.isNotBlank(filter.getBranchType())){
            sqlcount.append(" and branch_type = '"+filter.getBranchType()+"' ");
        }
        if(!StringUtil.isEmpty(filter.getCheckObject())){
            sqlcount.append(" and S.check_object like '%" + filter.getCheckObject() + "%' ");
        }
        if(!StringUtil.isEmpty(filter.getCheckDept())){
            sqlcount.append(" and S.check_dept like  '%" + filter.getCheckDept() + "%' ");
        }
        sqlcount.append(" group by check_dept ");
        SQLQuery queryCount = getSession().createSQLQuery(sqlcount.toString());
        List resultCount = queryCount.list();
        int total = resultCount.size();
        filter.getPaging().setPageCount(total/filter.getPaging().getPageSize() + 1);
        filter.getPaging().setRecords(total);
        StringBuffer sql = new StringBuffer();
        int fristnum = (filter.getPaging().getCurrentPage() - 1) * filter.getPaging().getPageSize();
//        int endnum = fristnum + filter.getPaging().getPageSize();
        int endnum = 10;
        sql.append("select sys_id sysId,check_dept checkDept," +
                "check_dept checkObject,summary_date summaryDate,avg(score) score " +
                "from t_scoresummary p where 1 = 1 ");
        if(StringUtils.isNotBlank(filter.getBranchType())){
            sql.append(" and branch_type = '"+filter.getBranchType()+"' ");
        }
        if(StringUtils.isNotBlank(filter.getCheckDept())){
            sql.append("and check_dept like " + StringUtils.getLikewords(filter.getCheckDept()));
        }
        if(StringUtils.isNotBlank(filter.getCheckObject())){
            sql.append("and check_object like " + StringUtils.getLikewords(filter.getCheckObject()));
        }
        if("asc".equals(filter.getPaging().getSortVal())){
            sql.append("group by check_dept order by avg(score) desc ");
        }else if("desc".equals(filter.getPaging().getSortVal())){
            sql.append("group by check_dept order by avg(score) asc ");
        }else{
            sql.append("group by check_dept order by avg(score) desc ");
        }

        sql.append("limit "+fristnum+"," + endnum);
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DepartRank.class));
        List<DepartRank> list = (List<DepartRank>) query.list();
        return list;
    }


    public List<DepartRank> queryDepartRankNoPage(ScoreSummaryFilter filter){

        StringBuffer sql = new StringBuffer();
        sql.append("select sys_id sysId,check_dept checkDept," +
                "check_dept checkObject,summary_date summaryDate,avg(score) score " +
                "from t_scoresummary p where 1 = 1 ");
        if(StringUtils.isNotBlank(filter.getBranchType())){
            sql.append(" and branch_type = '"+filter.getBranchType()+"' ");
        }
        if(StringUtils.isNotBlank(filter.getCheckDept())){
            sql.append("and check_dept like " + StringUtils.getLikewords(filter.getCheckDept()));
        }
        if(StringUtils.isNotBlank(filter.getCheckObject())){
            sql.append("and check_object like " + StringUtils.getLikewords(filter.getCheckObject()));
        }
        if(ObjectUtils.isNotBlank(filter.getPaging())){
            if("asc".equals(filter.getPaging().getSortVal())){
                sql.append("group by check_dept order by avg(score) desc ");
            }else if("desc".equals(filter.getPaging().getSortVal())){
                sql.append("group by check_dept order by avg(score) asc ");
            }else{
                sql.append("group by check_dept order by avg(score) desc ");
            }
        }else{
            sql.append("group by check_dept order by avg(score) desc ");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DepartRank.class));
        List<DepartRank> list = (List<DepartRank>) query.list();
        return list;
    }


}